﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace InsideDL.MISData
{
    /*
0YYYYMM
1客戶統編
2放款帳號
3當月貼補金額(TWD) 
4SOURCE      
     */

    /// <summary>
    /// 補貼息
    /// </summary>
    public class DataCIBSubsidyImport : DataMatrix
    {
        StringBuilder insertSB = null;


        public string buildInsertStatement(string p_sht, string p_path
            , string p_ym, bool p_del, string p_source,string p_sql)
        {
            string del, sql, vl;

            del = string.Empty;
            if (p_del.Equals(true))
            {
                del = string.Format("DELETE FROM [Subsidy] WHERE YYYYMM='{0}' AND source='{1}'"
                    , p_ym, p_source);
            }

            DataTable dt = getExcelData(p_sql, p_path);
            sql = "INSERT INTO [subsidy] VALUES('{0}','{1}',{2},'{3}')";

            insertSB = new StringBuilder();
            insertSB.Append("USE [MISDB]" + System.Environment.NewLine);
            insertSB.Append("GO" + System.Environment.NewLine);
            insertSB.Append(del + System.Environment.NewLine);
            List<string> rowList = null;

            foreach (DataRow row in dt.Rows)
            {
                rowList = new List<string>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    vl = row[i].ToString().Replace("'", "");
                    rowList.Add(string.Empty.Equals(vl) ? " " : vl);
                }
                rowList.Add(p_source);
                insertSB.AppendFormat(sql, rowList.ToArray());
                insertSB.Append(System.Environment.NewLine);
            }
            return insertSB.ToString();            
        }

        public string buildInsertStatement(string p_sht, string p_path
            ,string p_ym,bool p_del,string p_source)
        {
            string del,sql,vl;

            del = string.Empty;
            if (p_del.Equals(true))
            {
                del = string.Format("DELETE FROM [Subsidy] WHERE YYYYMM='{0}' AND source='{1}'"
                    , p_ym,p_source);
            }
            
            sql = string.Format(@"
SELECT
    {1} AS YYYYMM
    ,放款帳號
    ,SUM([當月貼補金額(TWD)]) AS 當月貼補金額
FROM [{0}$]
WHERE 客戶統編 IS NOT NULL
GROUP BY 放款帳號
", p_sht,p_ym);
            //WHERE 客戶統編 = 'PCBCCNBJ' 
            DataTable dt = getExcelData(sql, p_path);
             
            sql = "INSERT INTO [subsidy] VALUES('{0}','{1}','{2}',{3},'{4}')";
                

            insertSB = new StringBuilder();
            insertSB.Append("USE [MISDB]" + System.Environment.NewLine);
            insertSB.Append("GO" + System.Environment.NewLine);
            insertSB.Append(del + System.Environment.NewLine);
            List<string> rowList = null;
            
            foreach (DataRow row in dt.Rows)
            {
                rowList = new List<string>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    vl = row[i].ToString().Replace("'", "");
                    rowList.Add(string.Empty.Equals(vl) ? " " : vl);
                }
                rowList.Add(p_source);
                insertSB.AppendFormat(sql, rowList.ToArray());
                insertSB.Append(System.Environment.NewLine);
            }
            return insertSB.ToString();            
        }


        public string GetSQL(string p_ym, string p_tblName)
        {
            return string.Format(@"
SELECT
    {1} AS YYYYMM
    ,放款帳號
    ,SUM([當月貼補金額(TWD)]) AS 當月貼補金額
FROM [{0}$]
WHERE 客戶統編 IS NOT NULL
GROUP BY 放款帳號
", p_tblName, p_ym);
        }

        public string GetGOVSQL(string p_ym,string p_tblName,string p_mon)
        {
        /*    return string.Format(@"
SELECT
    {1} AS YYYYMM
    ,放款帳號
    ,SUM([{2}月補貼金額]) AS 當月貼補金額
FROM [{0}$D2:K200]
WHERE 放款帳號 IS NOT NULL
GROUP BY 放款帳號
", p_tblName, p_ym,p_mon);
         */
            return string.Format(@"
SELECT
    {1} AS YYYYMM
    ,放款帳號
    ,SUM(當月貼補金額(TWD)) AS 當月貼補金額
FROM [{0}$D2:K200]
WHERE 放款帳號 IS NOT NULL
GROUP BY 放款帳號
", p_tblName, p_ym);
        }

        /// <summary>
        /// 檢查字串是否為合法日期格式
        /// </summary>
        /// <param name="p_str"></param>
        /// <returns></returns>
        public string getDate(string p_str)
        {
            string dte = p_str;
            DateTime d1;
            if (!DateTime.TryParse(p_str, out d1))
            {
                dte = string.Format("{0}-{1}-{2}", p_str.Substring(0, 4)
                    , p_str.Substring(4, 2), p_str.Substring(6));
            }
            return dte;
        }
    }
}
